package com.tjsinfo.tjsoa.documentationflow.xingzhengshouwen.bo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.RowMapper;

import com.tjsinfo.tjsoa.Dao.IQbcDao;
import com.tjsinfo.tjsoa.Dao.ISpringDao;
import com.tjsinfo.tjsoa.common.ToolKit;
//import com.tjsinfo.tjsoa.documentationflow.jiweishouwen.vo.TJiWeishouwenNeirong;
import com.tjsinfo.tjsoa.documentationflow.xingzhengshouwen.dao.ITXingzhengshouwenNeirongDAO;
import com.tjsinfo.tjsoa.documentationflow.xingzhengshouwen.vo.ShouWenInfo;
import com.tjsinfo.tjsoa.documentationflow.xingzhengshouwen.vo.TXingzhengshouwenFujian;
import com.tjsinfo.tjsoa.documentationflow.xingzhengshouwen.vo.TXingzhengshouwenNeirong;
import com.tjsinfo.tjsoa.message.vo.TjsMessage;
import com.tjsinfo.tjsoa.system.vo.Files;
import com.tjsinfo.tjsoa.system.vo.TSystemPost;
import com.tjsinfo.tjsoa.system.vo.User;
import com.tjsinfo.tjsoa.workflow.WorkFlowTemplate;

public class TXingzhengshouwenNeirongBO implements ITXingzhengshouwenNeirongBO	{
	private ITXingzhengshouwenNeirongDAO  xingzhengshouwenNeirongDAO;
	private IQbcDao qbcDao;
	private ISpringDao springDao;
	
	
	public ISpringDao getSpringDao() {
		return springDao;
	}

	public void setSpringDao(ISpringDao springDao) {
		this.springDao = springDao;
	}

	public IQbcDao getQbcDao() {
		return qbcDao;
	}

	public void setQbcDao(IQbcDao qbcDao) {
		this.qbcDao = qbcDao;
	}
	
	public ITXingzhengshouwenNeirongDAO getXingzhengshouwenNeirongDAO() {
		return xingzhengshouwenNeirongDAO;
	}

	public void setXingzhengshouwenNeirongDAO(
			ITXingzhengshouwenNeirongDAO xingzhengshouwenNeirongDAO) {
		this.xingzhengshouwenNeirongDAO = xingzhengshouwenNeirongDAO;
	}

	public  String addTXingzhengshouweineirong(TXingzhengshouwenNeirong xingzhengshouwenNeirong,String qingbanren,List<Files> list,Integer userId) {
		// TODO Auto-generated method stub
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			Files file = (Files) iterator.next();
			TXingzhengshouwenFujian xingzhengshouwenFujian = new TXingzhengshouwenFujian();
			xingzhengshouwenFujian.setFujianAddress(file.toFilePath);
			xingzhengshouwenFujian.setFujianName(file.getOldFileName());
			xingzhengshouwenFujian.setFujianZhengname(file.getNewFileName());
			xingzhengshouwenNeirong.addFujian(xingzhengshouwenFujian);
		}
		String id = xingzhengshouwenNeirongDAO.save(xingzhengshouwenNeirong);
		WorkFlowTemplate flowTemplate = new WorkFlowTemplate();
		Long flowid = 0l;
		flowid = flowTemplate.creatProcessInstance("办公室主任批办", qingbanren, "行政收文");
		flowTemplate.setVariable("登记修改", userId.toString(), flowid);
		flowTemplate.setVariable("流程名称", "行政收文", flowid);
		xingzhengshouwenNeirong =	selectOneById(id);
		xingzhengshouwenNeirong.setFlowid(flowid.intValue());
		updateTXingzhengshouwenneirong(xingzhengshouwenNeirong);

	
		return id;
	}
	
	
	/**
	 * 添加行政收文内容 无流程登记用
	 * @param 党委发文
	 * @return 党委发文ID
	 */
	public String addTXingzhengshouweineirong(TXingzhengshouwenNeirong xingzhengshouwenNeirong,List<String> chengbanrenList,List<Files> list,Integer userId){
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			Files file = (Files) iterator.next();
			TXingzhengshouwenFujian xingzhengshouwenFujian = new TXingzhengshouwenFujian();
			xingzhengshouwenFujian.setFujianAddress(file.toFilePath);
			xingzhengshouwenFujian.setFujianName(file.getOldFileName());
			xingzhengshouwenFujian.setFujianZhengname(file.getNewFileName());
			xingzhengshouwenNeirong.addFujian(xingzhengshouwenFujian);
		}
		String id = xingzhengshouwenNeirongDAO.save(xingzhengshouwenNeirong);
		WorkFlowTemplate flowTemplate = new WorkFlowTemplate();
		Long flowid = 0l;
		
		flowid = flowTemplate.creatProcessInstance("待阅", chengbanrenList, "行政收文","to 待阅");		
		flowTemplate.setVariable("流程名称", "行政收文", flowid);
		xingzhengshouwenNeirong =	selectOneById(id);
		xingzhengshouwenNeirong.setFlowid(flowid.intValue());
		updateTXingzhengshouwenneirong(xingzhengshouwenNeirong);

		return id;
	}
	public TXingzhengshouwenNeirong selectOneById(String id) {
		// TODO Auto-generated method stub
		return xingzhengshouwenNeirongDAO.findById(id);
	}

	public Boolean updateTXingzhengshouwenneirong(TXingzhengshouwenNeirong xingzhengshouwenNeirong){
		// TODO Auto-generated method stub
		try {
			xingzhengshouwenNeirongDAO.merge(xingzhengshouwenNeirong);
			return true;
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	public List<TXingzhengshouwenNeirong> findByExample(
			TXingzhengshouwenNeirong xingzhengshouwenNeirong) {
		// TODO Auto-generated method stub
		List<TXingzhengshouwenNeirong> list = xingzhengshouwenNeirongDAO.findByExample(xingzhengshouwenNeirong);
		return list;
	}

	
	
	
	public Integer selectXingzhengshouwenTotalLine(){
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(TXingzhengshouwenNeirong.class);
		return  qbcDao.getQueryNumber(detachedCriteria, "id");
	}
	
	/**
	 * 查询行政收文的总行数
	 * @return
	 */
	public Integer selectXingzhengshouwenTotalLine(String laiwenjihuan,String laiwenzihao ,String zhaiyao){
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(TXingzhengshouwenNeirong.class);
		Disjunction disjunctionOr = Restrictions.disjunction();
		
		if(laiwenjihuan != null && !"".equals(laiwenjihuan)){
			disjunctionOr.add(Restrictions.like("laiwenjiguan", "%"+laiwenjihuan+"%"));
		}
		if(laiwenzihao != null && !"".equals(laiwenzihao)){
			disjunctionOr.add(Restrictions.like("laiwenzihao", "%"+laiwenzihao+"%"));
		}
		if(zhaiyao != null && !"".equals(zhaiyao)){
			disjunctionOr.add(Restrictions.like("zhaiyou", "%"+zhaiyao+"%"));
		}
		return  qbcDao.getQueryNumber(detachedCriteria, "id");
	}
	
	public List<TXingzhengshouwenNeirong> selectXingzhengshouwenPage(Integer first,Integer max){
		DetachedCriteria criteria = DetachedCriteria.forClass(TXingzhengshouwenNeirong.class);
		List<Order> orderlist = new ArrayList<Order>();
		orderlist.add(Order.desc("shouwenbianhao"));
		return qbcDao.findPageByCriteria(criteria,orderlist, max, first).getItems();
	}
	
	/**
	 * 查询行政收文分页
	 * @return
	 */
	public List<TXingzhengshouwenNeirong> selectXingzhengshouwenPage(Integer first,Integer max,String laiwenjihuan,String laiwenzihao ,String zhaiyao){
		DetachedCriteria criteria = DetachedCriteria.forClass(TXingzhengshouwenNeirong.class);
		Disjunction disjunctionOr = Restrictions.disjunction();
		
		if(laiwenjihuan != null && !"".equals(laiwenjihuan)){
			disjunctionOr.add(Restrictions.like("laiwenjiguan", "%"+laiwenjihuan+"%"));
		}
		if(laiwenzihao != null && !"".equals(laiwenzihao)){
			disjunctionOr.add(Restrictions.like("laiwenzihao", "%"+laiwenzihao+"%"));
		}
		if(zhaiyao != null && !"".equals(zhaiyao)){
			disjunctionOr.add(Restrictions.like("zhaiyou", "%"+zhaiyao+"%"));
		}
		criteria.add(disjunctionOr);
		List<Order> orderlist = new ArrayList<Order>();
		orderlist.add(Order.desc("shouwenbianhao"));
		return qbcDao.findPageByCriteria(criteria,orderlist, max, first).getItems();
	}
	
	public Integer selectXingzhengshouwenTotalLineCriteria(DetachedCriteria criteria){
		return  qbcDao.getQueryNumber(criteria, "id");
	}
	

	public List<TXingzhengshouwenNeirong> selectXingzhengshouwenPageCriteria(DetachedCriteria criteria, Integer first,Integer max){
		List<Order> orderlist = new ArrayList<Order>();
		orderlist.add(Order.desc("shouwenbianhao"));
		return qbcDao.findPageByCriteria(criteria,orderlist, max, first).getItems();
	}
	
	public Integer selectMaxZongShouWenHaoByYear(String shouwentype){
//		Integer endyear = date.getYear() + 1901;
//		Date betweendate = ToolKit.getDayByWeek(betweenyear, 1, true);
//		Date enddate = ToolKit.getDayByWeek(endyear, 1, true);
//		DetachedCriteria criteria = DetachedCriteria.forClass(TXingzhengshouwenNeirong.class);
//		criteria.add(Restrictions.between("shouwenriqi", betweendate, enddate));
//		criteria.addOrder(Order.desc("sortid"));
//		List<TXingzhengshouwenNeirong> list = qbcDao.getCriteria(criteria);
//		if(list.size()> 0){
//			TXingzhengshouwenNeirong neirong =  list.get(0);
//			Integer shouwenbianhao = Integer.parseInt(neirong.getShouwenbianhao()); 
//			shouwenbianhao += 1;
//			return shouwenbianhao;
//		}else{
//			return 0;
//		}
		StringBuffer sql = new StringBuffer("SELECT top(1) shouwenbianhao FROM t_xingzhengshouwen_neirong");
		sql.append("\n WHERE shouwentype=?");
		sql.append("\n ORDER BY sortid DESC");
		List<String> tempList = springDao.querySQL(sql.toString(), new Object[]{shouwentype}, new RowMapper(){
			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getString("shouwenbianhao");
			}
		});
		Integer maxbianhao=1;
		if (tempList.size()>0){
			maxbianhao=Integer.parseInt(tempList.get(0))+1;   //收文编号
		}
		return maxbianhao;
	}

	/**
	 * 删除
	 * @param xingzhengshouwenNeirong
	 * @return
	 */
	public Boolean deleteXingzhengshouwen(TXingzhengshouwenNeirong xingzhengshouwenNeirong){
		try {
			xingzhengshouwenNeirongDAO.delete(xingzhengshouwenNeirong);
			return true;
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public List<TXingzhengshouwenNeirong> selectDownXingzhengshouwenNeirong(Integer userId,String proname){
		String sql =  new String("EXEC selectdownxingzhengfawen @dataBaseId = ?,@subUserId = ?,@proname = ?");
		List<TXingzhengshouwenNeirong> countList = springDao.querySQL(sql, new Object[]{1,userId,proname}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				TXingzhengshouwenNeirong xingzhengshouwenNeirong = new TXingzhengshouwenNeirong();
				xingzhengshouwenNeirong.setZhaiyou(arg0.getString("biaoti"));
				xingzhengshouwenNeirong.setLaiwenriqi(arg0.getDate("fawendatetime"));
				xingzhengshouwenNeirong.setLaiwenjiguan(arg0.getString("laiwendanwei"));
				xingzhengshouwenNeirong.setLaiwenzihao(arg0.getString("wenhaotype"));
				xingzhengshouwenNeirong.setDataBaseId(arg0.getInt("dataBaseId"));
				xingzhengshouwenNeirong.setId(arg0.getString("docid"));
				xingzhengshouwenNeirong.setTaskId(arg0.getLong("taskid"));
				return xingzhengshouwenNeirong;
			}
			
		});
		return countList;
	}
	
	public TXingzhengshouwenNeirong selectDownXingzhengshouwenNeirongById(Integer dataBaseId,String id){
		String sql =  new String("EXEC selectXingZhengDownFaWenInfo @dataBase = ?,@id = ?");
		List<TXingzhengshouwenNeirong> countList = springDao.querySQL(sql, new Object[]{dataBaseId,id}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				TXingzhengshouwenNeirong xingzhengshouwenNeirong = new TXingzhengshouwenNeirong();
				xingzhengshouwenNeirong.setZhaiyou(arg0.getString("biaoti"));
				xingzhengshouwenNeirong.setLaiwenriqi(arg0.getDate("fawendatetime"));
				xingzhengshouwenNeirong.setLaiwenjiguan(arg0.getString("laiwendanwei"));
				xingzhengshouwenNeirong.setLaiwenzihao(arg0.getString("wenhaotype"));
				xingzhengshouwenNeirong.setDataBaseId(arg0.getInt("dataBaseId"));
				xingzhengshouwenNeirong.setDocId(arg0.getString("docname"));
				return xingzhengshouwenNeirong;
			}
			
		});
		return countList.get(0);
	}
	
	public List<TXingzhengshouwenFujian>  selectDownXingzhengshouwenFuJian(Integer dataBaseId,String id){
		String sql =  new String("EXEC selectXingZhengDownFaWenFuJian @dataBase = ?,@id = ?");
		List<TXingzhengshouwenFujian> countList = springDao.querySQL(sql, new Object[]{dataBaseId,id}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				TXingzhengshouwenFujian xingzhengshouwenFujian = new TXingzhengshouwenFujian();
				xingzhengshouwenFujian.setFujianAddress(arg0.getString("fujian_address"));
				xingzhengshouwenFujian.setFujianName(arg0.getString("fujian_name"));
				xingzhengshouwenFujian.setFujianZhengname(arg0.getString("fujian_zhengname"));
				return xingzhengshouwenFujian;
			}
			
		});
		return countList;
	}
	
	public Boolean endTask(Integer dataBaseId,Integer id){
		String sql =  new String("EXEC endTask @dataBase = ?,@id = ?");
		try {
			springDao.commonUpdate(sql, new Object[]{dataBaseId,id});
		
			return true;
		} catch (RuntimeException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	public List<ShouWenInfo> selectQingBanTask(Integer userId){
		String sql =  new String("EXEC selectqingbantask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public List<ShouWenInfo> selectChuanYueTask(Integer userId){
		String sql =  new String("EXEC selectchuanyuetask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public List<ShouWenInfo> selectDengJiXiuGaiTask(Integer userId){
		String sql =  new String("EXEC selectdengjixiugaitask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public List<ShouWenInfo> selectJiYaoBanLiTask(Integer userId){
		String sql =  new String("EXEC selectjiyaobanlitask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public List<ShouWenInfo> selectBuMenChengBanTask(Integer userId){
		String sql =  new String("EXEC selectbumenchengbantask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public List<ShouWenInfo> selectShouWenFanKuiTask(Integer userId){
		String sql =  new String("EXEC selectfankuitask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public List<ShouWenInfo> selectShouWenBanLiZhongTask(Integer userId){
		String sql =  new String("EXEC selectshowenbanlizhong @userid = ?,@taskuserid = ?");
		List<ShouWenInfo> shouwenlist = new ArrayList<ShouWenInfo>();
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{"%,"+userId+",%",userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setFlowId(arg0.getInt("flowid"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				shouWenInfo.setWeizhi(arg0.getString("Name_"));
				shouWenInfo.setPeople(arg0.getString("username"));
				return shouWenInfo;
			}
			
		});
		for (ShouWenInfo shouWenInfo : countList) {
			if ("部门承办".equals(shouWenInfo.getWeizhi()) ) {
				List<User> list =  selectShouWenUser(shouWenInfo.getFlowId());
				shouWenInfo.setPeople("");
				for (User user : list) {
					shouWenInfo.setPeople(shouWenInfo.getPeople()+" "+user.getUsername());
				}
				shouwenlist.add(shouWenInfo);
			}else{
				shouwenlist.add(shouWenInfo);
			}
			
		}
		return shouwenlist;
	}
	
	
	
	public List<ShouWenInfo> selectDaibanTask(Integer userId, String huanjie){
		String sql =  new String("EXEC selectshouwendaibantask @actorid = ? ,@huanjie= ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId,huanjie}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	
	private  List<User> selectShouWenUser(Integer flowId){
		StringBuffer sql = new StringBuffer("SELECT systemuser.userName FROM dbo.t_system_user AS systemuser JOIN dbo.JBPM_TASKINSTANCE AS task ON task.ACTORID_ = systemuser.userId  ");
		sql.append("JOIN dbo.JBPM_PROCESSINSTANCE AS processinstance ON processinstance.ID_ = task.PROCINST_ ");
		sql.append("JOIN dbo.JBPM_PROCESSDEFINITION AS processdefinition ON processdefinition.ID_ = processinstance.PROCESSDEFINITION_ ");
		sql.append("WHERE task.ISOPEN_ = 1 AND task.ISSIGNALLING_ = 1 AND processdefinition.NAME_ LIKE '%收文%' AND processinstance.ID_ = ? ");
		List<User> countList = springDao.querySQL(sql.toString(), new Object[]{flowId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				User user = new User();
				user.setUsername(arg0.getString("userName"));
				return user;
			}
			
		});
		return countList;
	}
	public List<ShouWenInfo> selectChaYueWenJian(Integer userId,Integer first,Integer max,String laiwenjiguan,String laiwenzihao,String zhaiyou,String tablename,String subtype){
		String procname="";
		if ("xingzhengshouwen".equals(tablename)){
			procname="shouwenchayuewenjianxz";
		}else{
			procname="shouwenchayuewenjiandw";
		}
		String sql =  new String("EXEC "+procname+" @first= ?,@limit = ?,@userid = ?,@useridlike = ?,@laiwenjiguan = ?,@laiwenzihao = ?,@zhaiyou = ?,@subtype = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{first,max,userId,"%,"+userId+",%",laiwenjiguan,laiwenzihao,zhaiyou,subtype}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setLaiwenjiguan(arg0.getString("laiwenjiguan"));
				shouWenInfo.setLaiwenriqi(ToolKit.FormatDate(arg0.getDate("laiwenriqi")) );
				shouWenInfo.setTableName(arg0.getString("tablename"));
				shouWenInfo.setZhuangtai(arg0.getString("zhuangtai"));
				String shouwenbh=buLing(arg0.getString("shouwenbianhao"));  //给收文编号补零
				shouWenInfo.setShouwenbianhao(arg0.getString("shouwentype")+"年"+shouwenbh+"号");
				return shouWenInfo;
			}
		});
		return countList;
	}
	
	private String buLing(String str_m) {
		// TODO Auto-generated method stub
		  String str ="0000";
		  str_m=str.substring(0, 4-str_m.length())+str_m;
		  return str_m;
	}	
	
	public Integer selectChaYueWenJianCount(Integer userId,Integer first,Integer max,String laiwenjiguan,String laiwenzihao,String zhaiyou,String tablename,String subtype){
		String procname="";
		if ("xingzhengshouwen".equals(tablename)){
			procname="shouwenchayuewenjianCountxz";
		}else{
			procname="shouwenchayuewenjianCountdw";
		}
		String sql =  new String("EXEC "+procname+" @first= ?,@limit = ?,@userid = ?,@useridlike = ?,@laiwenjiguan = ?,@laiwenzihao = ?,@zhaiyou = ?,@subtype = ?");
		List<Integer> countList = springDao.querySQL(sql, new Object[]{first,max,userId,"%,"+userId+",%",laiwenjiguan,laiwenzihao,zhaiyou,subtype}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				
				return arg0.getInt("count");
			}
			
		});
		return countList.get(0);
	}
	
	public List<ShouWenInfo> selectXingZhengShouWenGengZong(Integer first,Integer max,String laiwenjiguan,String laiwenzihao,String zhaiyou){
		String sql =  new String("EXEC xingzhengshouwengengzong  @first= ?,@limit = ?,@laiwenjiguan = ?,@laiwenzihao = ?,@zhaiyou = ?,@zhuangtai = ?");
		if (laiwenjiguan != null) {
			laiwenjiguan = "%"+laiwenjiguan+"%";
		}
		if (laiwenzihao != null) {
			laiwenzihao = "%"+laiwenzihao+"%";
		}
		if (zhaiyou != null) {
			zhaiyou = "%"+zhaiyou+"%";
		}
		
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{first,max,laiwenjiguan,laiwenzihao,zhaiyou,null}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setLaiwenjiguan(arg0.getString("laiwenjiguan"));
				shouWenInfo.setLaiwenriqi(ToolKit.FormatDate(arg0.getDate("laiwenriqi")) );
				shouWenInfo.setTableName(arg0.getString("tablename"));
				shouWenInfo.setFlowId(arg0.getInt("flowid"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public Integer selectXingZhengShouWenGengZongCount(Integer first,Integer max,String laiwenjiguan,String laiwenzihao,String zhaiyou){
		String sql =  new String("EXEC xingzhengshouwengengzongCount  @first= ?,@limit = ?,@laiwenjiguan = ?,@laiwenzihao = ?,@zhaiyou = ?,@zhuangtai = ?");
		if (laiwenjiguan != null) {
			laiwenjiguan = "%"+laiwenjiguan+"%";
		}
		if (laiwenzihao != null) {
			laiwenzihao = "%"+laiwenzihao+"%";
		}
		if (zhaiyou != null) {
			zhaiyou = "%"+zhaiyou+"%";
		}
		List<Integer> countList = springDao.querySQL(sql, new Object[]{first,max,laiwenjiguan,laiwenzihao,zhaiyou,null}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getInt("countint");
			}
			
		});
		return countList.get(0);
	}
	
	public List<ShouWenInfo> selectDangWeiShouWenGengZong(Integer first,Integer max,String laiwenjiguan,String laiwenzihao,String zhaiyou){
		String sql =  new String("EXEC dangweishouwengengzong  @first= ?,@limit = ?,@laiwenjiguan = ?,@laiwenzihao = ?,@zhaiyou = ?,@zhuangtai = ?");
		if (laiwenjiguan != null) {
			laiwenjiguan = "%"+laiwenjiguan+"%";
		}
		if (laiwenzihao != null) {
			laiwenzihao = "%"+laiwenzihao+"%";
		}
		if (zhaiyou != null) {
			zhaiyou = "%"+zhaiyou+"%";
		}
		
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{first,max,laiwenjiguan,laiwenzihao,zhaiyou,null}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setLaiwenjiguan(arg0.getString("laiwenjiguan"));
				shouWenInfo.setLaiwenriqi(ToolKit.FormatDate(arg0.getDate("laiwenriqi")) );
				shouWenInfo.setTableName(arg0.getString("tablename"));
				shouWenInfo.setFlowId(arg0.getInt("flowid"));
				return shouWenInfo;
			}
			
		});
		return countList;
	}
	
	public Integer selectDangWeiShouWenGengZongCount(Integer first,Integer max,String laiwenjiguan,String laiwenzihao,String zhaiyou){
		String sql =  new String("EXEC dangweishouwengengzongCount  @first= ?,@limit = ?,@laiwenjiguan = ?,@laiwenzihao = ?,@zhaiyou = ?,@zhuangtai = ?");
		if (laiwenjiguan != null) {
			laiwenjiguan = "%"+laiwenjiguan+"%";
		}
		if (laiwenzihao != null) {
			laiwenzihao = "%"+laiwenzihao+"%";
		}
		if (zhaiyou != null) {
			zhaiyou = "%"+zhaiyou+"%";
		}
		List<Integer> countList = springDao.querySQL(sql, new Object[]{first,max,laiwenjiguan,laiwenzihao,zhaiyou,null}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getInt("countint");
			}
			
		});
		return countList.get(0);
	}
	
	public List<String> selectAllUserId(){
		StringBuffer sql = new StringBuffer("SELECT userId FROM dbo.t_system_user");
		List<String> userIdList =  springDao.querySQL(sql.toString(), new Object[]{}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getString("userId");
			}
			
		});
		
		return userIdList;
	}
	
	public Integer isChuanYueCount(Integer userId, Integer flowId){
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) AS taskcount FROM dbo.JBPM_TASKINSTANCE AS taskinstance   	WHERE taskinstance.ISOPEN_ = 1 AND taskinstance.NAME_  =  ? AND taskinstance.ACTORID_ = ? AND taskinstance.PROCINST_ = ? ");
		List<Integer> userIdList =  springDao.querySQL(sql.toString(), new Object[]{"传阅",userId,flowId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				return arg0.getInt("taskcount");
			}
			
		});
		
		return userIdList.get(0);
	}
	
//	public List<TXingzhengshouwenNeirong> selectXingZhengShouWenDaiBan(Integer userId){
//		List<TXingzhengshouwenNeirong> list = new ArrayList<TXingzhengshouwenNeirong>();
//		StringBuffer sql = new StringBuffer("EXEC xingzhengshouwendaiban  @userId = ?");
//		list = springDao.querySQL(sql.toString(), new Object[]{userId}, new RowMapper(){
//
//			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
//				// TODO Auto-generated method stub
//				TXingzhengshouwenNeirong xingzhengshouwenNeirong = new TXingzhengshouwenNeirong();
//				xingzhengshouwenNeirong.setId(arg0.getString("id"));
//				xingzhengshouwenNeirong.setTaskId(arg0.getLong("ID_"));
//				xingzhengshouwenNeirong.setDangqianhuanjie(arg0.getString("NAME_"));
//				xingzhengshouwenNeirong.setZhaiyou(arg0.getString("zhaiyou"));
//				return xingzhengshouwenNeirong;
//			}
//			
//		});
//		return list;
//	}
	
	/**
	 * 查询流程上一个环节
	 * @param flowid
	 * @return
	 */
	public String selectTopTask(Integer flowid){
		List<String> list = new ArrayList<String>();
 		StringBuffer sql = new StringBuffer("SELECT TOP 1 task.NAME_ AS taskname FROM dbo.JBPM_LOG  AS LOG_ JOIN dbo.JBPM_TASKINSTANCE AS task ON LOG_.TASKINSTANCE_ = task.ID_ ");
		sql.append("WHERE LOG_.TOKEN_  = ? AND LOG_.TASKINSTANCE_ IS NOT NULL AND LOG_.TASKACTORID_ IS NULL ORDER BY task.ID_ DESC ");
		list = springDao.querySQL(sql.toString(), new Object[]{flowid}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				
				return arg0.getString("taskname");
			}
			
		});
		if (list.size() > 0 ) {
			return list.get(0);
		} else {
			return "传阅";
		}
	}
	
	public Boolean deleteTask(Integer taskId){
		try {
			StringBuffer sql = new StringBuffer("delete from  dbo.JBPM_LOG WHERE TASKINSTANCE_ = ?  ");
			springDao.commonUpdate(sql.toString(), new Object[]{taskId});
			 sql = new StringBuffer("delete from JBPM_TASKINSTANCE where ID_ = ? ");
			springDao.commonUpdate(sql.toString(), new Object[]{taskId});
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
	}

	public List<ShouWenInfo> selectDaibanTask(Integer userId) {
		String sql =  new String("EXEC selectshouwentask @actorid = ?");
		List<ShouWenInfo> countList = springDao.querySQL(sql, new Object[]{userId}, new RowMapper(){

			public Object mapRow(ResultSet arg0, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ShouWenInfo shouWenInfo = new ShouWenInfo();
				shouWenInfo.setId(arg0.getString("id"));
				shouWenInfo.setTaskId(arg0.getInt("taskId"));
				shouWenInfo.setLaiwenbiaoti(arg0.getString("zhaiyou"));
				shouWenInfo.setLaiwenzihao(arg0.getString("laiwenzihao"));
				shouWenInfo.setTableName(arg0.getString("tablename"));
				shouWenInfo.setNodename(arg0.getString("nodename"));
				return shouWenInfo;
			}
			
		});
		return countList;
		
		// TODO Auto-generated method stub
	}
}
